﻿//============================================================
//author:zhxitao
//============================================================

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Saker.Kernel.Model;

namespace Saker.Kernel.DAL
{
    #region SysUser数据访问层
    /// <summary>
    ///SysUser数据访问层
    /// </summary>	
    public partial class SysUserDAL
    {
        #region SysUser数据访问层 自动生成
        /// <summary>
        /// 添加SysUser
        /// </summary>
        /// <param name="SysUser">SysUser</param>
        /// <returns>添加后的SysUser</returns>
        public SysUser Add
            (SysUser sysUser)
        {
            string sql = "INSERT INTO SysUser (UserName, Password, RealName, Question, Answer, RoleID, DepartID, UserGroup, CreateTime, LastLoginTime, Status, IsOnline, IsLimit)  VALUES (@UserName, @Password, @RealName, @Question, @Answer, @RoleID, @DepartID, @UserGroup, @CreateTime, @LastLoginTime, @Status, @IsOnline, @IsLimit) select @@identity";
            SqlParameter[] para = new SqlParameter[]
					{
						new SqlParameter("@UserName", ToDBValue(sysUser.UserName)),
						new SqlParameter("@Password", ToDBValue(sysUser.Password)),
						new SqlParameter("@RealName", ToDBValue(sysUser.RealName)),
						new SqlParameter("@Question", ToDBValue(sysUser.Question)),
						new SqlParameter("@Answer", ToDBValue(sysUser.Answer)),
						new SqlParameter("@RoleID", ToDBValue(sysUser.RoleID)),
						new SqlParameter("@DepartID", ToDBValue(sysUser.DepartID)),
						new SqlParameter("@UserGroup", ToDBValue(sysUser.UserGroup)),
						new SqlParameter("@CreateTime", ToDBValue(sysUser.CreateTime)),
						new SqlParameter("@LastLoginTime", ToDBValue(sysUser.LastLoginTime)),
						new SqlParameter("@Status", ToDBValue(sysUser.Status)),
						new SqlParameter("@IsOnline", ToDBValue(sysUser.IsOnline)),
						new SqlParameter("@IsLimit", ToDBValue(sysUser.IsLimit)),
					};

            int newId = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, para));
            return GetByUserID(newId);
        }

        /// <summary>
        /// 根据主键删除SysUser
        /// </summary>
        /// <param name="UserID">UserID</param>
        /// <returns>影响的行数</returns>
        public int DeleteByUserID(int userID)
        {
            string sql = "DELETE SysUser WHERE UserID = @UserID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", userID)
			};

            return SqlHelper.ExecuteNonQuery(sql, para);
        }


        /// <summary>
        /// 更新SysUser
        /// </summary>
        /// <param name="SysUser">SysUser</param>
        /// <returns></returns>
        public int Update(SysUser sysUser)
        {
            string sql =
                "UPDATE SysUser " +
                "SET " +
            " UserName = @UserName"
                + ", Password = @Password"
                + ", RealName = @RealName"
                + ", Question = @Question"
                + ", Answer = @Answer"
                + ", RoleID = @RoleID"
                + ", DepartID = @DepartID"
                + ", UserGroup = @UserGroup"
                + ", CreateTime = @CreateTime"
                + ", LastLoginTime = @LastLoginTime"
                + ", Status = @Status"
                + ", IsOnline = @IsOnline"
                + ", IsLimit = @IsLimit"

            + " WHERE UserID = @UserID";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", sysUser.UserID)
					,new SqlParameter("@UserName", ToDBValue(sysUser.UserName))
					,new SqlParameter("@Password", ToDBValue(sysUser.Password))
					,new SqlParameter("@RealName", ToDBValue(sysUser.RealName))
					,new SqlParameter("@Question", ToDBValue(sysUser.Question))
					,new SqlParameter("@Answer", ToDBValue(sysUser.Answer))
					,new SqlParameter("@RoleID", ToDBValue(sysUser.RoleID))
					,new SqlParameter("@DepartID", ToDBValue(sysUser.DepartID))
					,new SqlParameter("@UserGroup", ToDBValue(sysUser.UserGroup))
					,new SqlParameter("@CreateTime", ToDBValue(sysUser.CreateTime))
					,new SqlParameter("@LastLoginTime", ToDBValue(sysUser.LastLoginTime))
					,new SqlParameter("@Status", ToDBValue(sysUser.Status))
					,new SqlParameter("@IsOnline", ToDBValue(sysUser.IsOnline))
					,new SqlParameter("@IsLimit", ToDBValue(sysUser.IsLimit))
			};

            return SqlHelper.ExecuteNonQuery(sql, para);
        }

        /// <summary>
        /// 根据主键查询SysUser实体
        /// </summary>
        /// <param name="SysUser">SysUser</param>
        /// <returns>SysUser</returns>
        public SysUser GetByUserID(int userID)
        {
            string sql = "SELECT * FROM SysUser WHERE UserID = @UserID";
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@UserID", userID)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        /// <summary>
        /// SqlDataReader对象转换为SysUser实体
        /// </summary>
        /// <param name="reader">SqlDataReader对象</param>
        /// <returns>SysUser实体</returns>
        public SysUser ToModel(SqlDataReader reader)
        {
            SysUser sysUser = new SysUser();

            sysUser.UserID = (int)ToModelValue(reader, "UserID");
            sysUser.UserName = (string)ToModelValue(reader, "UserName");
            sysUser.Password = (string)ToModelValue(reader, "Password");
            sysUser.RealName = (string)ToModelValue(reader, "RealName");
            sysUser.Question = (string)ToModelValue(reader, "Question");
            sysUser.Answer = (string)ToModelValue(reader, "Answer");
            sysUser.RoleID = (int)ToModelValue(reader, "RoleID");
            sysUser.DepartID = (int?)ToModelValue(reader, "DepartID");
            sysUser.UserGroup = (int)ToModelValue(reader, "UserGroup");
            sysUser.CreateTime = (DateTime)ToModelValue(reader, "CreateTime");
            sysUser.LastLoginTime = (DateTime?)ToModelValue(reader, "LastLoginTime");
            sysUser.Status = (int)ToModelValue(reader, "Status");
            sysUser.IsOnline = (bool)ToModelValue(reader, "IsOnline");
            sysUser.IsLimit = (bool)ToModelValue(reader, "IsLimit");
            return sysUser;
        }
        /// <summary>
        /// 得到数据总行数
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        {
            string sql = "SELECT count(1) FROM SysUser";
            return (int)SqlHelper.ExecuteScalar(sql);
        }
        /// <summary>
        /// 分页，此方法仅在sql 2005下使用
        /// </summary>
        /// <param name="minrownum">起始行数</param>
        /// <param name="maxrownum">>结束行数</param>
        /// <returns>SysUser集合</returns>
        public IEnumerable<SysUser> GetPagedData(int minrownum, int maxrownum)
        {
            string sql = "SELECT * from(SELECT *,row_number() over(order by UserID) rownum FROM SysUser) t where rownum>=@minrownum and rownum<=@maxrownum";
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,
                new SqlParameter("@minrownum", minrownum),
                new SqlParameter("@maxrownum", maxrownum)))
            {
                return ToModels(reader);
            }
        }
        /// <summary>
        /// 分页，调用存储过程
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="recordCount">记录数</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="whereCondition">where</param>
        /// <returns>DataTable数据集</returns>
        public DataTable GetPagingRecord(int pageIndex, ref int recordCount, int pageSize, ref int pageCount, string whereCondition)
        {
            return SqlHelper.GetPagingRecord(pageIndex, ref recordCount, pageSize, ref pageCount, "SysUser", "UserID", whereCondition, "*", "UserID", true);
        }
        /// <summary>
        /// 得到所有SysUser集合
        /// </summary>
        /// <returns>所有集合</returns>
        public IEnumerable<SysUser> GetAll()
        {
            string sql = "SELECT * FROM SysUser";
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql))
            {
                return ToModels(reader);
            }
        }
        /// <summary>
        /// SqlDataReader对象转换到SysUser集合
        /// </summary>
        /// <param name="reader">SqlDataReader对象</param>
        /// <returns>SysUser集合</returns>
        protected IEnumerable<SysUser> ToModels(SqlDataReader reader)
        {
            var list = new List<SysUser>();
            while (reader.Read())
            {
                list.Add(ToModel(reader));
            }
            return list;
        }
        /// <summary>
        /// 转换数据值
        /// </summary>
        /// <param name="value">object类型</param>
        /// <returns>object对象</returns>
        protected object ToDBValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
        /// <summary>
        /// 转换实体值
        /// </summary>
        /// <param name="reader">SqlDataReader对象</param>
        /// <param name="columnName">列名称</param>
        /// <returns>值</returns>
        protected object ToModelValue(SqlDataReader reader, string columnName)
        {
            if (reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                return null;
            }
            else
            {
                return reader[columnName];
            }
        }
        #endregion


        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="name">用户名</param>
        /// <param name="pass">密码</param>
        /// <returns>用户实体</returns>
        public SysUser UserLogin(string name, string pass)
        {
            string sql = "SELECT * FROM SysUser WHERE UserName=@UserName AND Password=@Password";
            SqlParameter[] para = new SqlParameter[]
					{
						new SqlParameter("@UserName", ToDBValue(name)),
						new SqlParameter("@Password", ToDBValue(pass)),
					};
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, para))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
    }
    #endregion
}
